import openpyxl
import datetime
import operator
import os
import sys

# dirPath = os.path.dirname(os.path.realpath(sys.executable))
timeCur = datetime.datetime.now()
dirPath = 'E:\\日常工作\\10月\\25\\每日必办'
print(dirPath)

fileWeiJieJue = dirPath + '\\今日未解决工单明细.xlsx'
fileResultSave = dirPath + '\\每日必办文案-工单回访情况.txt'
fileResultContent = "工单回访情况\r\n{}月{}日9时至{}月{}日9时，".format(timeCur.month, timeCur.day - 1, timeCur.month, timeCur.day)

bookWeiJieJue = openpyxl.load_workbook(fileWeiJieJue)
sheetWeiJieJue = bookWeiJieJue.active
maxRowWeiJieJue = sheetWeiJieJue.max_row
maxColWeiJieJue = sheetWeiJieJue.max_column
print('maxRowWeiJieJue:{}'.format(maxRowWeiJieJue))   # 打印表的行数
print('maxColWeiJieJue:{}'.format(maxColWeiJieJue))   # 打印表的列数
print(fileResultContent)


# 获取某一列的索引号 col_name 列名
def get_col_index(sheet_source, col_name):
    maxColSheetSource = sheet_source.max_column
    for colSource in range(1, maxColSheetSource+1):
        colCellValue = sheet_source.cell(1, colSource).value
        if colCellValue is not None:
            colCellValue = colCellValue.strip()
            if colCellValue == col_name:
                return colSource


# 修改特定的承办部门
def xiu_gai_cheng_ban_bu_men(sheet_source, col_title, col_source, col_dest):
    colTitleIndex = get_col_index(sheet_source, col_title)
    maxRowCur = sheet_source.max_row
    for rowCur in range(2, maxRowCur+1):
        cellValueSearch = sheet_source.cell(rowCur, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            if cellValueSearch == col_source:
                sheet_source.cell(rowCur, colTitleIndex).value = col_dest


# 修改承办部门
xiu_gai_cheng_ban_bu_men(sheetWeiJieJue, '承办部门', '应急小分队', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetWeiJieJue, '承办部门', '流动人口管理组', '社会治理组')
xiu_gai_cheng_ban_bu_men(sheetWeiJieJue, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
xiu_gai_cheng_ban_bu_men(sheetWeiJieJue, '承办部门', '送变电公司社区', '送变电社区')

# 获取有效回访件数
colIndex = get_col_index(sheetWeiJieJue, '回访状态')
maxRowSource = sheetWeiJieJue.max_row
numYouXiao = 0
for rowSource in range(2, maxRowSource+1):
    cellValue = sheetWeiJieJue.cell(rowSource, colIndex).value
    if cellValue is not None:
        cellValue = cellValue.strip()
        if cellValue == '有效回访':
            numYouXiao = numYouXiao+1
fileResultContent = fileResultContent + '区级有效回访工单{}件，'.format(numYouXiao)
print('区级有效回访工单{}件，'.format(numYouXiao))

# 计算 双满工单数量 解决基本满意数量 解决不满意数量
colIndexJieJueManYi = get_col_index(sheetWeiJieJue, '是否解决\n是否满意')
colIndexChengBan = get_col_index(sheetWeiJieJue, '承办部门')
colIndexTitle = get_col_index(sheetWeiJieJue, '标题')
colIndexArea = get_col_index(sheetWeiJieJue, '片区')

# 解决 满意
numDoubleOk = 0
# 解决 基本满意
numJiBenManYi = 0
keyListJiBenManYi = []
tinyDictJiBenManYiNum = {}
tinyDictJiBenManYiTitle = {}
# 解决 不满意
numJieJueBuManYi = 0
keyListBuManYi = []
tinyDictBuManYiNum = {}
tinyDictBuManYiTitle = {}
# 未解决 科室
numNoKeShi = 0
keyListNoKeShi = []
dictNoKeShiNum = {}
# 未解决 社区/村
numNoSheQu = 0
keyListNoSheQu = []
dictNoSheQuNum = {}

for rowSource in range(2, maxRowSource+1):
    cellValue = sheetWeiJieJue.cell(rowSource, colIndexJieJueManYi).value
    cellChengBan = sheetWeiJieJue.cell(rowSource, colIndexChengBan).value
    cellValueYouXiao = sheetWeiJieJue.cell(rowSource, colIndex).value
    if cellValueYouXiao != '有效回访':
        continue
    if cellChengBan is not None:
        isFindChengGuan = cellChengBan.find('综合城管')
        if isFindChengGuan == 0:
            cellChengBan = '综合行政执法队'
        cellArea = sheetWeiJieJue.cell(rowSource, colIndexArea).value
        cellTitle = sheetWeiJieJue.cell(rowSource, colIndexTitle).value
        if cellValue is not None:
            cellValue = cellValue.strip()
            if cellChengBan is not None:
                cellChengBan = cellChengBan.strip()
                if cellTitle is not None:
                    cellTitle = cellTitle.strip()
                    print('{}'.format(cellValue))
                    if cellValue == '解决\n满意' or cellValue == '解决\n非常满意':
                        numDoubleOk = numDoubleOk+1
                        print('{}-------true'.format(cellValue))
                        continue
                    if cellValue == '解决\n基本满意':
                        numJiBenManYi = numJiBenManYi + 1
                        if cellChengBan in keyListJiBenManYi:
                            tinyDictJiBenManYiNum[cellChengBan] = tinyDictJiBenManYiNum[cellChengBan] + 1
                            cellTitleOld = tinyDictJiBenManYiTitle[cellChengBan]
                            tinyDictJiBenManYiTitle[cellChengBan] = cellTitleOld + ',' + cellTitle + '问题1件'
                        else:
                            keyListJiBenManYi.append(cellChengBan)
                            tinyDictJiBenManYiNum[cellChengBan] = 1
                            tinyDictJiBenManYiTitle[cellChengBan] = cellTitle + '问题'
                        continue
                    if cellValue == '解决\n不满意' or cellValue == '解决\n非常不满意':
                        numJieJueBuManYi = numJieJueBuManYi + 1
                        if cellChengBan in keyListBuManYi:
                            tinyDictBuManYiNum[cellChengBan] = tinyDictBuManYiNum[cellChengBan] + 1
                            cellTitleOld = tinyDictBuManYiTitle[cellChengBan]
                            tinyDictBuManYiTitle[cellChengBan] = cellTitleOld + '、' + cellTitle + '问题1件'
                        else:
                            keyListBuManYi.append(cellChengBan)
                            tinyDictBuManYiNum[cellChengBan] = 1
                            tinyDictBuManYiTitle[cellChengBan] = cellTitle + '问题'
                    else:
                        # print('-{}-'.format(cellChengBan))
                        if cellArea is not None:
                            cellArea = cellArea.strip()
                            cellArea = cellArea.replace(' ', '')
                            if cellArea == '科室':
                                numNoKeShi = numNoKeShi+1
                                if cellChengBan in keyListNoKeShi:
                                    dictNoKeShiNum[cellChengBan] = dictNoKeShiNum[cellChengBan] + 1
                                else:
                                    keyListNoKeShi.append(cellChengBan)
                                    dictNoKeShiNum[cellChengBan] = 1
                                print('numNoKeShi：{} {}：{}'.format(numNoKeShi, cellChengBan, dictNoKeShiNum[cellChengBan]))
                            else:
                                numNoSheQu = numNoSheQu + 1
                                if cellChengBan in keyListNoSheQu:
                                    dictNoSheQuNum[cellChengBan] = dictNoSheQuNum[cellChengBan] + 1
                                else:
                                    keyListNoSheQu.append(cellChengBan)
                                    dictNoSheQuNum[cellChengBan] = 1


print('其中双满工单{}件；'.format(numDoubleOk))
print('解决基本满意工单{}件'.format(numJiBenManYi))
fileResultContent = fileResultContent + '其中双满工单{}件；'.format(numDoubleOk)

if numJiBenManYi > 0:
    fileResultContent = fileResultContent + '解决基本满意工单{}件（'.format(numJiBenManYi)
    tinyDictJiBenManYiNum = sorted(tinyDictJiBenManYiNum.items(), key=lambda kv: kv[1], reverse=True)
    for key in tinyDictJiBenManYiNum:
        value = '{}{}件：{}；'.format(key[0], key[1], tinyDictJiBenManYiTitle[key[0]])
        fileResultContent = fileResultContent + value
        print('{}'.format(value))
    fileResultContent = fileResultContent + '）。'

if numJieJueBuManYi > 0:
    fileResultContent = fileResultContent + '解决不满意工单{}件（'.format(numJieJueBuManYi)
    tinyDictBuManYiNum = sorted(tinyDictBuManYiNum.items(), key=lambda kv: kv[1], reverse=True)
    print('{}'.format(tinyDictBuManYiNum))
    for keyBuManYi in tinyDictBuManYiNum:
        value = '{}{}件：{}；'.format(keyBuManYi[0], keyBuManYi[1], tinyDictBuManYiTitle[keyBuManYi[0]])
        fileResultContent = fileResultContent + value
        print('{}'.format(value))
    fileResultContent = fileResultContent + '）。'

if numNoKeShi + numNoSheQu > 0:
    fileResultContent = fileResultContent + '\n未解决工单{}件:其中难以解决工单X件：XXXX件：' \
                                            'XXXX。（详见附件4）。\n'.format(numNoKeShi + numNoSheQu)
    if numNoKeShi > 0:
        fileResultContent = fileResultContent + '经督办申请二次回访0件，剩余未解决工单{}件：' \
                                                '其中涉及机关科室{}件（'.format(numNoKeShi + numNoSheQu, numNoKeShi)
        dictNoKeShiNum = sorted(dictNoKeShiNum.items(), key=lambda kv: kv[1], reverse=True)
        print('{}'.format(dictNoKeShiNum))
        numLast = 0
        numCount = 0
        numIndex = 0
        for keyNoKeShi in dictNoKeShiNum:
            numIndex = numIndex+1
            if numLast == 0:
                numLast = keyNoKeShi[1]
            else:
                if numLast != keyNoKeShi[1]:
                    if numCount > 1:
                        fileResultContent = fileResultContent + '各{}件，'.format(numLast)
                    else:
                        fileResultContent = fileResultContent + '{}件，'.format(numLast)
                    numCount = 0
            if numLast == keyNoKeShi[1]:
                if numCount == 0:
                    fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
                else:
                    fileResultContent = fileResultContent + '、{}'.format(keyNoKeShi[0])
            else:
                fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
            numCount = numCount+1
            numLast = keyNoKeShi[1]
        if numCount > 1:
            fileResultContent = fileResultContent + '各{}件'.format(numLast)
        else:
            fileResultContent = fileResultContent + '{}件'.format(numLast)
        fileResultContent = fileResultContent + '）。'
    if numNoSheQu > 0:
        fileResultContent = fileResultContent + '涉及社区（村）{}件（'.format(numNoSheQu)
        dictNoSheQuNum = sorted(dictNoSheQuNum.items(), key=lambda kv: kv[1], reverse=True)
        print('{}'.format(dictNoSheQuNum))
        numLastSheQu = 0
        numIndexSheQu = 0
        numCountSheQu = 0
        for keyNoSheQu in dictNoSheQuNum:
            numIndexSheQu = numIndexSheQu + 1
            if numLastSheQu == 0:
                numLastSheQu = keyNoSheQu[1]
            else:
                if numLastSheQu != keyNoSheQu[1]:
                    if numCountSheQu > 1:
                        fileResultContent = fileResultContent + '各{}件，'.format(numLastSheQu)
                    else:
                        fileResultContent = fileResultContent + '{}件，'.format(numLastSheQu)
                    numCountSheQu = 0
            if numLastSheQu == keyNoSheQu[1]:
                if numCountSheQu == 0:
                    fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
                else:
                    fileResultContent = fileResultContent + '、{}'.format(keyNoSheQu[0])
            else:
                fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
            numCountSheQu = numCountSheQu+1
            numLastSheQu = keyNoSheQu[1]
        if numCountSheQu > 1:
            fileResultContent = fileResultContent + '各{}件'.format(numLastSheQu)
        else:
            fileResultContent = fileResultContent + '{}件'.format(numLastSheQu)
        fileResultContent = fileResultContent + '）。详见附件3。'

with open(fileResultSave, "w") as f:
    f.write(fileResultContent)
